<?php
/* 
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

function doQueryEtudiantsParEntreprises($conn,$formation)
{
//  $queryString="(select
//                  entrepriseRef,
//                  count(*)
//                from fa_referent join contrat on referentRef=referentCle
//                      join fa_etudiant on etudCle=etudRef
//                      join fa_groupe on groupeRef=groupeCle
//
//                where '$formation' like concat('%',formationRef,'%')
//                group by entrepriseRef order by entrepriseRef)
//  union (
//        select
//                  'TOTAL' as entrepriseRef,
//                  count(*)
//                from fa_referent join contrat on referentRef=referentCle
//                      join fa_etudiant on etudCle=etudRef
//                      join fa_groupe on groupeRef=groupeCle
//
//                where '$formation' like concat('%',formationRef,'%')
//  )
//  ";
   $queryString="select
                  entrepriseRef,
                  count(*)
                from referent join contrat c on referentRef=referentCle
                          join etudiant_groupe eg on eg.etudRef=c.etudRef and annee=anneeCle
                                                    and annee=".$_SESSION[REF_YEAR]."
                          join etudiant on etudCle=c.etudRef
                          join groupe on groupeRef=groupeCle

                where '$formation' like concat('%',formationRef,'%')
                group by entrepriseRef order by entrepriseRef";
   
   $result=mysql_query($queryString,$conn);
   //echo $queryString."<br/>".mysql_error()."<br/>";

   return $result;
}

function doQueryEtudiantsParOPCAs($conn,$formation)
{

   $queryString="select
                  opcaRef,
                  count(*)
                from contrat c
                          join etudiant_groupe eg on eg.etudRef=c.etudRef and annee=anneeCle
                                                    and annee=".$_SESSION[REF_YEAR]."
                          join etudiant on etudCle=c.etudRef
                          join groupe on groupeRef=groupeCle

                where '$formation' like concat('%',formationRef,'%')
                group by opcaRef order by opcaRef";
   //echo $queryString;

   $result=mysql_query($queryString,$conn);
   return $result;
}

function doQueryEncadrementTuteurEtudiants($conn,$formation)
{
  $queryString=" select count(*), t.nb from
                  (select
                      tuteurRef,
                      count(tuteurRef) as nb
                    from contrat c
                          join etudiant_groupe eg on eg.etudRef=c.etudRef and annee=anneeCle
                                                    and annee=".$_SESSION[REF_YEAR]."
                          join etudiant on etudCle=c.etudRef
                          join groupe on groupeRef=groupeCle

                    where length(tuteurRef)>0 and '$formation' like concat('%',formationRef,'%')
                group by tuteurRef order by count(tuteurRef)) t group by t.nb order by t.nb desc ;
  ";

   //echo $queryString;

   $result=mysql_query($queryString,$conn);
   return $result;
}

function doQueryEtudiantsParFormation($conn,$formation)
{
  $queryString=" select t1.formationRef, t1.nb, t2.nbSans from (select
                      formationRef,
                      count(*) as nb
                    from contrat c
                          join etudiant_groupe eg on eg.etudRef=c.etudRef and annee=anneeCle
                                                    and annee=".$_SESSION[REF_YEAR]."
                          join etudiant on etudCle=c.etudRef
                          join groupe on groupeRef=groupeCle
                    where '$formation' like concat('%',formationRef,'%')
                group by formationRef) t1 natural left join
                (select
                      formationRef,
                      count(*) as nbSans
                    from contrat c
                          join etudiant_groupe eg on eg.etudRef=c.etudRef and annee=anneeCle
                                                    and annee=".$_SESSION[REF_YEAR]."
                          join etudiant on etudCle=c.etudRef
                           join groupe on groupeRef=groupeCle

                    where length(ifnull(tuteurRef,''))=0 and '$formation' like concat('%',formationRef,'%')
                group by formationRef) t2 ;
  ";

   //echo $queryString;
   $result=mysql_query($queryString,$conn);
   //echo mysql_error();
   return $result;
}

function doQueryEncadrementEtudiantsEntr($conn,$formation)
{
  $queryString=" select t1.formationRef, t1.nb, t2.nbSans from (select
                      formationRef,
                      count(*) as nb
                    from contrat c
                          join etudiant_groupe eg on eg.etudRef=c.etudRef and annee=anneeCle
                                                    and annee=".$_SESSION[REF_YEAR]."
                          join etudiant on etudCle=c.etudRef
                          join groupe on groupeRef=groupeCle

                    where length(ifnull(tuteurRef,''))=0 and '$formation' like concat('%',formationRef,'%')
                group by formationRef) t1 natural left join
                (select
                      formationRef,
                      count(*) as nbSans
                    from contrat c
                          join etudiant_groupe eg on eg.etudRef=c.etudRef and annee=anneeCle
                                                    and annee=".$_SESSION[REF_YEAR]."
                          join etudiant on etudCle=c.etudRef
                          join groupe on groupeRef=groupeCle
                          join referent on referentRef=referentCle

                    where length(ifnull(tuteurRef,''))=0 and entrepriseRef='__sans entreprise__' and '$formation' like concat('%',formationRef,'%')
                group by formationRef) t2 ;
  ";

   //echo $queryString;

   $result=mysql_query($queryString,$conn);
   return $result;
}

?>
